{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\lib\\site-packages\\sklearn\\cross_validation.py:41: DeprecationWarning: This module was deprecated in version 0.18 in favor of the model_selection module into which all the refactored classes and functions are moved. Also note that the interface of the new CV iterators are different from that of this module. This module will be removed in 0.20.\n",
      "  \"This module will be removed in 0.20.\", DeprecationWarning)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<script type=\"text/javascript\">window.PlotlyConfig = {MathJaxConfig: 'local'};</script><script type=\"text/javascript\">if (window.MathJax) {MathJax.Hub.Config({SVG: {font: \"STIX-Web\"}});}</script><script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window._Plotly) {require(['plotly'],function(plotly) {window._Plotly=plotly;});}</script>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "#%matplotlib inline\n",
    "import pickle as pk\n",
    "import csv\n",
    "import datetime\n",
    "import lightgbm as lgb\n",
    "from scipy import stats\n",
    "from scipy.sparse import hstack, csr_matrix\n",
    "from sklearn.model_selection import train_test_split\n",
    "from wordcloud import WordCloud\n",
    "from collections import Counter\n",
    "\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn import preprocessing\n",
    "from sklearn.cross_validation import train_test_split\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import classification_report\n",
    "import plotly.offline as py\n",
    "py.init_notebook_mode(connected=True)\n",
    "import plotly.graph_objs as go\n",
    "import plotly.tools as tls\n",
    "\n",
    "#from xgboost import XGBClassifier\n",
    "import lightgbm as lgb\n",
    "from sklearn import model_selection\n",
    "from sklearn.metrics import accuracy_score\n",
    "import re\n",
    "import time\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X_tr = pd.read_csv('D:/transaction_risk_competition/transaction_train_new.csv')\n",
    "test_X_tr = pd.read_csv('D:/transaction_risk_competition/transaction_round1_new.csv')\n",
    "label_train = pd.read_csv('D:/transaction_risk_competition/tag_train_new.csv')\n",
    "label_test = pd.read_csv('D:/transaction_risk_competition/sumbit_uid.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len(train_X_tr) 264654\n",
      "len(test_X_tr) 168981\n",
      "len(label_train) 31179\n",
      "len(label_test) 31198\n"
     ]
    }
   ],
   "source": [
    "print(\"len(train_X_tr)\",len(train_X_tr))\n",
    "print(\"len(test_X_tr)\",len(test_X_tr))\n",
    "print(\"len(label_train)\",len(label_train))\n",
    "print(\"len(label_test)\",len(label_test))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "len(train_test_tr) 433635\n"
     ]
    }
   ],
   "source": [
    "train_test_tr=pd.concat([train_X_tr,test_X_tr])\n",
    "print(\"len(train_test_tr)\",len(train_test_tr))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "del train_X_tr\n",
    "del test_X_tr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "#pd.set_option('display.max_rows', None)\n",
    "#设置value的显示长度为100，默认为50\n",
    "pd.set_option('max_colwidth',100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_test_tr=train_test_tr[['UID','day','time','trans_amt','bal','acc_id1','acc_id2','acc_id3','amt_src1','amt_src2','trans_type2','trans_type1','market_code','market_type','merchant','channel','device_code1','device_code2','device_code3','device1','device2','mac1','ip1','ip1_sub','geo_code']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>bal</th>\n",
       "      <th>acc_id1</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>channel</th>\n",
       "      <th>device_code1</th>\n",
       "      <th>device_code2</th>\n",
       "      <th>device_code3</th>\n",
       "      <th>device1</th>\n",
       "      <th>device2</th>\n",
       "      <th>mac1</th>\n",
       "      <th>ip1</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>geo_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>30</td>\n",
       "      <td>11:48:47</td>\n",
       "      <td>1459</td>\n",
       "      <td>1007</td>\n",
       "      <td>544dfdeedad0ae67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>acdbdb842ac20f1e</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>102.0</td>\n",
       "      <td>26bcf43a19df14c8</td>\n",
       "      <td>2ecf94369847c748</td>\n",
       "      <td>1.0</td>\n",
       "      <td>abc6bc660561e0eb</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102</td>\n",
       "      <td>ffda8eaa413171cb</td>\n",
       "      <td>b36097d8a79e5b43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>d2cf44cec09806cc</td>\n",
       "      <td>VIVO X9L</td>\n",
       "      <td>7aaec0f23bfa60ea</td>\n",
       "      <td>a664530c1fb20abe</td>\n",
       "      <td>45f4ca1f4e9b9cb7</td>\n",
       "      <td>wsbq</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>23</td>\n",
       "      <td>16:58:03</td>\n",
       "      <td>725</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7a87b1aa20972d48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>0e305dafdd3af261</td>\n",
       "      <td>365ec16ad4006c4f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>NEM-AL10</td>\n",
       "      <td>6f675a9b2124d2f8</td>\n",
       "      <td>52abe1e300a8aa10</td>\n",
       "      <td>2b416f1d7e89ad3b</td>\n",
       "      <td>wte7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>22</td>\n",
       "      <td>09:56:39</td>\n",
       "      <td>127</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>cfd035fc7e0a53b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>429d5656ba8f3d01</td>\n",
       "      <td>760cfbfc0c2e3b49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a92470e4f878a3f4</td>\n",
       "      <td>M3X</td>\n",
       "      <td>99a5f1ff7f3e54f0</td>\n",
       "      <td>e4c132d02ff6c095</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>ws7n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>1</td>\n",
       "      <td>18:11:45</td>\n",
       "      <td>99317</td>\n",
       "      <td>100</td>\n",
       "      <td>516e596377b2019c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a571c7fda8b7df37</td>\n",
       "      <td>fbf6bf3c8927414c</td>\n",
       "      <td>0.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>c71c876b8979028e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>609954adce541306</td>\n",
       "      <td>31989e264685a18c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>KNT-UL10</td>\n",
       "      <td>a8dc52f65085212e</td>\n",
       "      <td>e1c78a56cc9dfc8b</td>\n",
       "      <td>cf1527a0b4173e8f</td>\n",
       "      <td>wqj6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>22</td>\n",
       "      <td>13:04:02</td>\n",
       "      <td>3361</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6628f5ca013e3582</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2f61e8f52151bd8b</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>abb6b4b8f15be6d6</td>\n",
       "      <td>cbc62c2b1cb7deb9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  day      time  trans_amt   bal           acc_id1 acc_id2 acc_id3  \\\n",
       "0  19092   30  11:48:47       1459  1007  544dfdeedad0ae67     NaN     NaN   \n",
       "1  13465   23  16:58:03        725   100               NaN     NaN     NaN   \n",
       "2  13713   22  09:56:39        127   100               NaN     NaN     NaN   \n",
       "3  22703    1  18:11:45      99317   100  516e596377b2019c     NaN     NaN   \n",
       "4  17816   22  13:04:02       3361   100               NaN     NaN     NaN   \n",
       "\n",
       "           amt_src1          amt_src2  trans_type2       trans_type1  \\\n",
       "0  acdbdb842ac20f1e  9fefed0a981dcb7a        102.0  26bcf43a19df14c8   \n",
       "1  4d7831c6f695ab19               NaN        105.0  c2f2023d279665b2   \n",
       "2  c5fc631370cabc0d               NaN        105.0  c2f2023d279665b2   \n",
       "3  a571c7fda8b7df37  fbf6bf3c8927414c          0.0  c2f2023d279665b2   \n",
       "4  c5fc631370cabc0d               NaN        105.0  c2f2023d279665b2   \n",
       "\n",
       "        market_code  market_type          merchant code1 code2  channel  \\\n",
       "0  2ecf94369847c748          1.0  abc6bc660561e0eb   NaN   NaN      102   \n",
       "1               NaN          0.0  7a87b1aa20972d48   NaN   NaN      140   \n",
       "2               NaN          0.0  cfd035fc7e0a53b2   NaN   NaN      140   \n",
       "3               NaN          0.0  c71c876b8979028e   NaN   NaN      140   \n",
       "4               NaN          0.0  6628f5ca013e3582   NaN   NaN      140   \n",
       "\n",
       "       device_code1      device_code2      device_code3           device1  \\\n",
       "0  ffda8eaa413171cb  b36097d8a79e5b43               NaN  d2cf44cec09806cc   \n",
       "1  0e305dafdd3af261  365ec16ad4006c4f               NaN  09baf2f39bc3dc86   \n",
       "2  429d5656ba8f3d01  760cfbfc0c2e3b49               NaN  a92470e4f878a3f4   \n",
       "3  609954adce541306  31989e264685a18c               NaN  09baf2f39bc3dc86   \n",
       "4               NaN               NaN  2f61e8f52151bd8b  aca4977fbe8741e1   \n",
       "\n",
       "    device2              mac1               ip1           ip1_sub geo_code  \n",
       "0  VIVO X9L  7aaec0f23bfa60ea  a664530c1fb20abe  45f4ca1f4e9b9cb7     wsbq  \n",
       "1  NEM-AL10  6f675a9b2124d2f8  52abe1e300a8aa10  2b416f1d7e89ad3b     wte7  \n",
       "2       M3X  99a5f1ff7f3e54f0  e4c132d02ff6c095  ff71a556d86c4b8f     ws7n  \n",
       "3  KNT-UL10  a8dc52f65085212e  e1c78a56cc9dfc8b  cf1527a0b4173e8f     wqj6  \n",
       "4  IPHONE 5               NaN  abb6b4b8f15be6d6  cbc62c2b1cb7deb9        0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#选取做因子化\n",
    "##amt_src1 交易类型，余额，花呗做因子化\n",
    "##trans_type2\t线上线下 ，一定要因子化，只有4中，加上none,5中\n",
    "##market_type\t，营销类型，只有2种，加上空值，三种，一定要因子化\n",
    "##channel 平台，渠道，5种，加上空值6种，适合因子化\n",
    "values={'market_type':0,'channel':0,'trans_type2':0,'trans_type1':'0','amt_src1':'0','geo_code':'0'} \n",
    "train_test_tr.fillna(value=values,inplace = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>bal</th>\n",
       "      <th>acc_id1</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>channel</th>\n",
       "      <th>device_code1</th>\n",
       "      <th>device_code2</th>\n",
       "      <th>device_code3</th>\n",
       "      <th>device1</th>\n",
       "      <th>device2</th>\n",
       "      <th>mac1</th>\n",
       "      <th>ip1</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>geo_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90698</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:23:56</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134052</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:24:17</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>9287</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>154</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42602</th>\n",
       "      <td>10001</td>\n",
       "      <td>3</td>\n",
       "      <td>07:18:17</td>\n",
       "      <td>15186</td>\n",
       "      <td>61204</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>102.0</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>a18cb3d84b9ec04c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>bca1b486effef686</td>\n",
       "      <td>8e6d45472f232ed4</td>\n",
       "      <td>wssu</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  day      time  trans_amt    bal           acc_id1 acc_id2  \\\n",
       "90698   10000   26  12:23:56       5536    100               NaN     NaN   \n",
       "134052  10000   26  12:24:17       5536    100               NaN     NaN   \n",
       "63298   10001    2  08:31:13       9287    100               NaN     NaN   \n",
       "98298   10001    2  08:31:13        154    100               NaN     NaN   \n",
       "42602   10001    3  07:18:17      15186  61204  65407f8f309309a6     NaN   \n",
       "\n",
       "       acc_id3          amt_src1          amt_src2  trans_type2  \\\n",
       "90698      NaN  f29829bc82459191  9a8ee16bde15e38a        105.0   \n",
       "134052     NaN  f29829bc82459191  9a8ee16bde15e38a        105.0   \n",
       "63298      NaN  4d7831c6f695ab19               NaN        105.0   \n",
       "98298      NaN  c5fc631370cabc0d               NaN        105.0   \n",
       "42602      NaN  155c9e1c32bd0fa2  9fefed0a981dcb7a        102.0   \n",
       "\n",
       "             trans_type1 market_code  market_type          merchant code1  \\\n",
       "90698   c2f2023d279665b2         NaN          0.0  88aa547576f43f85   NaN   \n",
       "134052  c2f2023d279665b2         NaN          0.0  88aa547576f43f85   NaN   \n",
       "63298   c2f2023d279665b2         NaN          0.0  f3237791ad270d6b   NaN   \n",
       "98298   c2f2023d279665b2         NaN          0.0  f3237791ad270d6b   NaN   \n",
       "42602   6d55c54c8b1056fb         NaN          0.0  a18cb3d84b9ec04c   NaN   \n",
       "\n",
       "       code2  channel      device_code1      device_code2      device_code3  \\\n",
       "90698    NaN      140               NaN               NaN  deb68f7028682008   \n",
       "134052   NaN      140               NaN               NaN  deb68f7028682008   \n",
       "63298    NaN      140  e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "98298    NaN      140  e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "42602    NaN      102  e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "\n",
       "                 device1          device2              mac1               ip1  \\\n",
       "90698   aca4977fbe8741e1         IPHONE 5               NaN  34c715882de1a508   \n",
       "134052  aca4977fbe8741e1         IPHONE 5               NaN  34c715882de1a508   \n",
       "63298   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  2c1958d001a993d9   \n",
       "98298   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  2c1958d001a993d9   \n",
       "42602   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  bca1b486effef686   \n",
       "\n",
       "                 ip1_sub geo_code  \n",
       "90698   1e3ea9498c461cbf        0  \n",
       "134052  1e3ea9498c461cbf        0  \n",
       "63298   bd5e6a16e61e61f7     wsgb  \n",
       "98298   bd5e6a16e61e61f7     wsgb  \n",
       "42602   8e6d45472f232ed4     wssu  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr.sort_values(by=['UID','day','time'],inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>bal</th>\n",
       "      <th>acc_id1</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>channel</th>\n",
       "      <th>device_code1</th>\n",
       "      <th>device_code2</th>\n",
       "      <th>device_code3</th>\n",
       "      <th>device1</th>\n",
       "      <th>device2</th>\n",
       "      <th>mac1</th>\n",
       "      <th>ip1</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>geo_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90698</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:23:56</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134052</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:24:17</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>9287</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>154</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42602</th>\n",
       "      <td>10001</td>\n",
       "      <td>3</td>\n",
       "      <td>07:18:17</td>\n",
       "      <td>15186</td>\n",
       "      <td>61204</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>102.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>a18cb3d84b9ec04c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>bca1b486effef686</td>\n",
       "      <td>8e6d45472f232ed4</td>\n",
       "      <td>wssu</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  day      time  trans_amt    bal           acc_id1 acc_id2  \\\n",
       "90698   10000   26  12:23:56       5536    100               NaN     NaN   \n",
       "134052  10000   26  12:24:17       5536    100               NaN     NaN   \n",
       "63298   10001    2  08:31:13       9287    100               NaN     NaN   \n",
       "98298   10001    2  08:31:13        154    100               NaN     NaN   \n",
       "42602   10001    3  07:18:17      15186  61204  65407f8f309309a6     NaN   \n",
       "\n",
       "       acc_id3  amt_src1          amt_src2  trans_type2  trans_type1  \\\n",
       "90698      NaN         3  9a8ee16bde15e38a        105.0            9   \n",
       "134052     NaN         3  9a8ee16bde15e38a        105.0            9   \n",
       "63298      NaN         1               NaN        105.0            9   \n",
       "98298      NaN         0               NaN        105.0            9   \n",
       "42602      NaN         2  9fefed0a981dcb7a        102.0            4   \n",
       "\n",
       "       market_code  market_type          merchant code1 code2  channel  \\\n",
       "90698          NaN            0  88aa547576f43f85   NaN   NaN      140   \n",
       "134052         NaN            0  88aa547576f43f85   NaN   NaN      140   \n",
       "63298          NaN            0  f3237791ad270d6b   NaN   NaN      140   \n",
       "98298          NaN            0  f3237791ad270d6b   NaN   NaN      140   \n",
       "42602          NaN            0  a18cb3d84b9ec04c   NaN   NaN      102   \n",
       "\n",
       "            device_code1      device_code2      device_code3  \\\n",
       "90698                NaN               NaN  deb68f7028682008   \n",
       "134052               NaN               NaN  deb68f7028682008   \n",
       "63298   e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "98298   e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "42602   e56819f72c9b7860  1aa9250077f98fea               NaN   \n",
       "\n",
       "                 device1          device2              mac1               ip1  \\\n",
       "90698   aca4977fbe8741e1         IPHONE 5               NaN  34c715882de1a508   \n",
       "134052  aca4977fbe8741e1         IPHONE 5               NaN  34c715882de1a508   \n",
       "63298   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  2c1958d001a993d9   \n",
       "98298   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  2c1958d001a993d9   \n",
       "42602   09baf2f39bc3dc86  HUAWEI GRA-CL00  8e98757bf596ee35  bca1b486effef686   \n",
       "\n",
       "                 ip1_sub geo_code  \n",
       "90698   1e3ea9498c461cbf        0  \n",
       "134052  1e3ea9498c461cbf        0  \n",
       "63298   bd5e6a16e61e61f7     wsgb  \n",
       "98298   bd5e6a16e61e61f7     wsgb  \n",
       "42602   8e6d45472f232ed4     wssu  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr['market_type'], uniques =pd.factorize(train_test_tr['market_type'],sort=True)\n",
    "del uniques\n",
    "train_test_tr['trans_type1'], uniques =pd.factorize(train_test_tr['trans_type1'],sort=True)\n",
    "del uniques\n",
    "a=train_test_tr['amt_src1'].value_counts().index.tolist()\n",
    "b=[0,1,2,3,4,5,6,7,8,9,10]+([-1]*(len(a)-11))\n",
    "dicts=dict(zip(a,b))\n",
    "train_test_tr['amt_src1']=train_test_tr['amt_src1'].map(dicts)\n",
    "del a,b,dicts\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>bal</th>\n",
       "      <th>acc_id1</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>merchant</th>\n",
       "      <th>channel</th>\n",
       "      <th>device_code1</th>\n",
       "      <th>device_code2</th>\n",
       "      <th>device_code3</th>\n",
       "      <th>device1</th>\n",
       "      <th>device2</th>\n",
       "      <th>mac1</th>\n",
       "      <th>ip1</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>geo_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90698</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:23:56</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134052</th>\n",
       "      <td>10000</td>\n",
       "      <td>26</td>\n",
       "      <td>12:24:17</td>\n",
       "      <td>5536</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>140</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>deb68f7028682008</td>\n",
       "      <td>aca4977fbe8741e1</td>\n",
       "      <td>IPHONE 5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34c715882de1a508</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>9287</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98298</th>\n",
       "      <td>10001</td>\n",
       "      <td>2</td>\n",
       "      <td>08:31:13</td>\n",
       "      <td>154</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>f3237791ad270d6b</td>\n",
       "      <td>140</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>2c1958d001a993d9</td>\n",
       "      <td>bd5e6a16e61e61f7</td>\n",
       "      <td>wsgb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42602</th>\n",
       "      <td>10001</td>\n",
       "      <td>3</td>\n",
       "      <td>07:18:17</td>\n",
       "      <td>15186</td>\n",
       "      <td>61204</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>102.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>a18cb3d84b9ec04c</td>\n",
       "      <td>102</td>\n",
       "      <td>e56819f72c9b7860</td>\n",
       "      <td>1aa9250077f98fea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>09baf2f39bc3dc86</td>\n",
       "      <td>HUAWEI GRA-CL00</td>\n",
       "      <td>8e98757bf596ee35</td>\n",
       "      <td>bca1b486effef686</td>\n",
       "      <td>8e6d45472f232ed4</td>\n",
       "      <td>wssu</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  day      time  trans_amt    bal           acc_id1 acc_id2  \\\n",
       "90698   10000   26  12:23:56       5536    100               NaN     NaN   \n",
       "134052  10000   26  12:24:17       5536    100               NaN     NaN   \n",
       "63298   10001    2  08:31:13       9287    100               NaN     NaN   \n",
       "98298   10001    2  08:31:13        154    100               NaN     NaN   \n",
       "42602   10001    3  07:18:17      15186  61204  65407f8f309309a6     NaN   \n",
       "\n",
       "       acc_id3  amt_src1          amt_src2  trans_type2  trans_type1  \\\n",
       "90698      NaN         3  9a8ee16bde15e38a        105.0            9   \n",
       "134052     NaN         3  9a8ee16bde15e38a        105.0            9   \n",
       "63298      NaN         1               NaN        105.0            9   \n",
       "98298      NaN         0               NaN        105.0            9   \n",
       "42602      NaN         2  9fefed0a981dcb7a        102.0            4   \n",
       "\n",
       "       market_code  market_type          merchant  channel      device_code1  \\\n",
       "90698          NaN            0  88aa547576f43f85      140               NaN   \n",
       "134052         NaN            0  88aa547576f43f85      140               NaN   \n",
       "63298          NaN            0  f3237791ad270d6b      140  e56819f72c9b7860   \n",
       "98298          NaN            0  f3237791ad270d6b      140  e56819f72c9b7860   \n",
       "42602          NaN            0  a18cb3d84b9ec04c      102  e56819f72c9b7860   \n",
       "\n",
       "            device_code2      device_code3           device1          device2  \\\n",
       "90698                NaN  deb68f7028682008  aca4977fbe8741e1         IPHONE 5   \n",
       "134052               NaN  deb68f7028682008  aca4977fbe8741e1         IPHONE 5   \n",
       "63298   1aa9250077f98fea               NaN  09baf2f39bc3dc86  HUAWEI GRA-CL00   \n",
       "98298   1aa9250077f98fea               NaN  09baf2f39bc3dc86  HUAWEI GRA-CL00   \n",
       "42602   1aa9250077f98fea               NaN  09baf2f39bc3dc86  HUAWEI GRA-CL00   \n",
       "\n",
       "                    mac1               ip1           ip1_sub geo_code  \n",
       "90698                NaN  34c715882de1a508  1e3ea9498c461cbf        0  \n",
       "134052               NaN  34c715882de1a508  1e3ea9498c461cbf        0  \n",
       "63298   8e98757bf596ee35  2c1958d001a993d9  bd5e6a16e61e61f7     wsgb  \n",
       "98298   8e98757bf596ee35  2c1958d001a993d9  bd5e6a16e61e61f7     wsgb  \n",
       "42602   8e98757bf596ee35  bca1b486effef686  8e6d45472f232ed4     wssu  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr=train_test_tr[['UID','day','time','trans_amt','bal','acc_id1','acc_id2','acc_id3','amt_src1','amt_src2','trans_type2','trans_type1','market_code','market_type','merchant','channel','device_code1','device_code2','device_code3','device1','device2','mac1','ip1','ip1_sub','geo_code']]\n",
    "train_test_tr.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 20)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid1_count=train_test_tr[['acc_id1','acc_id2','acc_id3','amt_src1','amt_src2','trans_type2','trans_type1','market_code','market_type','merchant','channel','device_code1','device_code2','device1','device2','mac1','ip1','ip1_sub','geo_code']].groupby(train_test_tr['UID']).count().add_suffix('_count').reset_index()\n",
    "train_test_tr_uid1_count.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 金额特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 16)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_channel_amt=train_test_tr.groupby(['UID','channel'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_channel_').unstack()\n",
    "train_test_tr_uid2_channel_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_channel_amt.columns.ravel()]\n",
    "train_test_tr_uid2_channel_amt.reset_index(inplace=True)\n",
    "train_test_tr_uid2_channel_amt.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_channel_amt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 10)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_market_type_amt=train_test_tr.groupby(['UID','market_type'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_market_type_').unstack()\n",
    "train_test_tr_uid2_market_type_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_market_type_amt.columns.ravel()]\n",
    "train_test_tr_uid2_market_type_amt.reset_index(inplace=True)\n",
    "train_test_tr_uid2_market_type_amt.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_market_type_amt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 16)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_trans_type2_amt=train_test_tr.groupby(['UID','trans_type2'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_trans_type2_').unstack()\n",
    "train_test_tr_uid2_trans_type2_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_trans_type2_amt.columns.ravel()]\n",
    "train_test_tr_uid2_trans_type2_amt.reset_index(inplace=True)\n",
    "train_test_tr_uid2_trans_type2_amt.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_trans_type2_amt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 49)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_trans_type1_amt=train_test_tr.groupby(['UID','trans_type1'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_trans_type1_').unstack()\n",
    "train_test_tr_uid2_trans_type1_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_trans_type1_amt.columns.ravel()]\n",
    "train_test_tr_uid2_trans_type1_amt.reset_index(inplace=True)\n",
    "train_test_tr_uid2_trans_type1_amt.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_trans_type1_amt.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 37)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_amt_src1_amt=train_test_tr.groupby(['UID','amt_src1'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_amt_src1_').unstack()\n",
    "train_test_tr_uid2_amt_src1_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_amt_src1_amt.columns.ravel()]\n",
    "train_test_tr_uid2_amt_src1_amt.reset_index(inplace=True)\n",
    "train_test_tr_uid2_amt_src1_amt.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_amt_src1_amt.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 余额特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 16)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_channel_bal=train_test_tr.groupby(['UID','channel'])['bal'].agg({'mean','max','min'}).add_prefix('amt_channel_').unstack()\n",
    "train_test_tr_uid2_channel_bal.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_channel_bal.columns.ravel()]\n",
    "train_test_tr_uid2_channel_bal.reset_index(inplace=True)\n",
    "train_test_tr_uid2_channel_bal.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_channel_bal.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 10)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_market_type_bal=train_test_tr.groupby(['UID','market_type'])['bal'].agg({'mean','max','min'}).add_prefix('amt_market_type_').unstack()\n",
    "train_test_tr_uid2_market_type_bal.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_market_type_bal.columns.ravel()]\n",
    "train_test_tr_uid2_market_type_bal.reset_index(inplace=True)\n",
    "train_test_tr_uid2_market_type_bal.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_market_type_bal.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 16)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_trans_type2_bal=train_test_tr.groupby(['UID','trans_type2'])['bal'].agg({'mean','max','min'}).add_prefix('amt_trans_type2_').unstack()\n",
    "train_test_tr_uid2_trans_type2_bal.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_trans_type2_bal.columns.ravel()]\n",
    "train_test_tr_uid2_trans_type2_bal.reset_index(inplace=True)\n",
    "train_test_tr_uid2_trans_type2_bal.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_trans_type2_bal.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 49)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_trans_type1_bal=train_test_tr.groupby(['UID','trans_type1'])['bal'].agg({'mean','max','min'}).add_prefix('amt_trans_type1_').unstack()\n",
    "train_test_tr_uid2_trans_type1_bal.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_trans_type1_bal.columns.ravel()]\n",
    "train_test_tr_uid2_trans_type1_bal.reset_index(inplace=True)\n",
    "train_test_tr_uid2_trans_type1_bal.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_trans_type1_bal.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 37)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid2_amt_src1_bal=train_test_tr.groupby(['UID','amt_src1'])['bal'].agg({'mean','max','min'}).add_prefix('amt_amt_src1_').unstack()\n",
    "train_test_tr_uid2_amt_src1_bal.columns = [x[0]+\"_\"+str(x[1]) for x in train_test_tr_uid2_amt_src1_bal.columns.ravel()]\n",
    "train_test_tr_uid2_amt_src1_bal.reset_index(inplace=True)\n",
    "train_test_tr_uid2_amt_src1_bal.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid2_amt_src1_bal.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 日期，平均每日交易次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 2)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid_day = train_test_tr[['UID', 'day']]\n",
    "train_test_tr_uid_day.drop_duplicates(['UID', 'day'], 'first', inplace=True)\n",
    "train_test_tr_uid1_day_count = train_test_tr_uid_day[['day']].groupby(train_test_tr_uid_day['UID']).count().add_suffix('_count').reset_index()\n",
    "train_test_tr_uid1_day_count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 2)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr['day_time'] = train_test_tr.apply(lambda x: str(x['day']) + \"_\" + str(x['time']) , axis=1)\n",
    "train_test_tr_uid1_day_time_count = train_test_tr[['day_time']].groupby(train_test_tr['UID']).count().add_suffix('_count').reset_index()\n",
    "train_test_tr_uid1_day_time_count.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 4)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_test_tr_uid1_count_day_avg = pd.merge(train_test_tr_uid1_day_time_count,train_test_tr_uid1_day_count,how='left',on='UID')\n",
    "train_test_tr_uid1_count_day_avg['count_day_avg']=train_test_tr_uid1_count_day_avg['day_time_count']/train_test_tr_uid1_count_day_avg['day_count']\n",
    "del train_test_tr_uid1_day_count,train_test_tr_uid1_day_time_count\n",
    "train_test_tr_uid1_count_day_avg.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 相邻两次交易时间差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 4)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#需要先排序\n",
    "train_test_tr.sort_values(by=['UID','day','time'],inplace=True)\n",
    "train_test_tr['day_shift'] = train_test_tr.groupby('UID')['day'].shift(-1)\n",
    "train_test_tr_uid_day_diff_avg_max_min=train_test_tr.groupby(['UID'])['day_shift'].agg({'mean','max','min'}).add_prefix('day_shift_').reset_index()\n",
    "train_test_tr_uid_day_diff_avg_max_min.fillna(0.0,inplace=True)\n",
    "train_test_tr_uid_day_diff_avg_max_min.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 特征merge left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60643, 319)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_tr_train_test = pd.merge(train_test_tr_uid1_count_day_avg, train_test_tr_uid_day_diff_avg_max_min, how='left', on='UID')\n",
    "\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_amt_src1_bal, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_trans_type1_bal, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_trans_type1_bal, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_market_type_bal, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_channel_bal, how='left', on='UID')\n",
    "\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_amt_src1_amt, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_trans_type1_amt, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_trans_type1_amt, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_market_type_amt, how='left', on='UID')\n",
    "feature_tr_train_test = pd.merge(feature_tr_train_test, train_test_tr_uid2_channel_amt, how='left', on='UID')\n",
    "\n",
    "feature_tr_train_test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " # 分别和训练集的label 和提交的uid 关联，分开\n",
    " \n",
    " \n",
    " "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
